package CCRMServer.Controllers;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import CCRMServer.DBConnect;
import Common.SERVER_COMMANDS;
import Common.ServerAnswer;

/**
 * This class implements the server side of the CCRM report sub-system
 * @author Ori Ziv
 *
 */
public class ServerReportsManager {

	/**
	 * This method generate the Customer characteristic report
	 * @return - Server answer with the report matrix or error message in case of failure
	 */
	public static ServerAnswer generateCustomersReport() {
		// create and execute queries
		String table = "Customers C";
		String fields = "C.CustomerID, C.CustomerName," +
				" (SELECT COUNT(*) FROM CustomersResponses CR WHERE C.customerID = CR.customerID) AS total," +
				" (SELECT COUNT(*) FROM CustomersResponses CR WHERE C.customerID = CR.customerID AND CR.responseType = 1) AS buys";
		Connection conDB = DBConnect.mySQLConnection();
		ArrayList<String[]> matrix = new ArrayList<String[]>();
		try {
			ResultSet rs = DBConnect.selectWithFilter(conDB, table, fields, null);

			// generate report matrix
			while (rs.next()) {
				// generate row
				String row[] = new String[5];
				row[0] = rs.getString(1);
				row[1] = rs.getString(2);
				int total = rs.getInt(3);
				row[2] = Integer.toString(total);
				int buys = rs.getInt(4);
				row[3] = Integer.toString(buys);
				float rate;
				if (total > 0)
					rate = (float)buys/total;
				else
					rate = 0;
				row[4] = Float.toString(rate);
				
				// add row to a sorted by rate matrix
				addToSortedMatrix(matrix, 0, row.clone());
			}
			return new ServerAnswer(SERVER_COMMANDS.CUSTOMERS_REPORT, matrix, "");
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.CUSTOMERS_REPORT, null, "DB Problem");
		}			
	}
	
	/**
	 * This method generate Sales report
	 * @param saleID - sale to report
	 * @return The report's query result
	 */
	public static ArrayList<ResultSet> generateSalesReport(String saleID){
		ArrayList<ResultSet> myrs = new ArrayList<ResultSet>();
		String table= "sales S";
		String filter= "saleID = " + saleID;
		String fields="S.saleID, S.saleName," +
		" (SELECT COUNT(customerID) FROM CustomersResponses CR WHERE S.saleID = CR.saleID) AS num_of_cust," +
		" (SELECT COUNT(responseType) FROM CustomersResponses CR WHERE S.saleID = CR.saleID AND CR.responseType = 1) AS buys," +
		" (SELECT SUM(responseDuration) FROM CustomersResponses CR WHERE S.saleID = CR.saleID) AS total_time"; 
		
		String table1="customersresponses";
		String filter1="saleID = " + saleID  +" AND " + "responseType = 0";
		String fields1=	"responseRefusalDescription";
		
		Connection conDB = DBConnect.mySQLConnection();
		try{
			ResultSet rs = DBConnect.selectWithFilter(conDB, table, fields,filter );
			ResultSet rs1 = DBConnect.selectWithFilter(conDB, table1, fields1,filter1 );
			myrs.add(rs);
			myrs.add(rs1);
			return myrs;
		} catch (SQLException e) {
			return null;
		}
	}
	/**
	 * This recursive method add a array of strings to a sorted array list of string arrays.
	 * The array list is sorted by the rate cell (5th cell in each element of the array list 
	 * @param arr - sorted array list of string arrays
	 * @param index
	 * @param row - row to add
	 */
	private static void addToSortedMatrix (ArrayList<String[]> arr, int index, String[] row) {
		if (arr.isEmpty() || index == arr.size() ||
				(index == 0 && Float.parseFloat(row[4]) > Float.parseFloat(arr.get(index)[4]) || 
				(index == 0 && Float.parseFloat(row[4]) == Float.parseFloat(arr.get(index)[4]) &&
				Integer.parseInt(row[2]) >= Integer.parseInt(arr.get(index)[2])))) {
			arr.add(index, row);
			return;
		}
				
		if (index > 0 && 
					(Float.parseFloat(row[4]) <= Float.parseFloat(arr.get(index - 1)[4]) &&
					Float.parseFloat(row[4]) > Float.parseFloat(arr.get(index)[4]))) {
			arr.add(index, row);
			return;
		}
		
		if (index > 0 &&
				(Float.parseFloat(row[4]) == Float.parseFloat(arr.get(index - 1)[4]) && 
						Float.parseFloat(row[4]) == Float.parseFloat(arr.get(index)[4]) &&
						Integer.parseInt(row[2]) >= Integer.parseInt(arr.get(index)[2]))) {
			arr.add(index, row);
			return;
		}
		
		if (index > 0 &&
				Float.parseFloat(row[4]) < Float.parseFloat(arr.get(index - 1)[4]) &&
				Float.parseFloat(row[4]) == Float.parseFloat(arr.get(index)[4]) &&
				Integer.parseInt(row[2]) >= Integer.parseInt(arr.get(index)[2])) {
			arr.add(index, row);
			return;
		}
		
		addToSortedMatrix(arr, index + 1, row);
	}

}
